
package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import java.sql.*;
import edu.byu.intex2.data.bo.*;
import java.util.List;
import java.util.LinkedList;

/**
 * A singleton object that CRUD's Sale objects.
 *
 * @author Conan C. Albrecht <conan@warp.byu.edu>
 * @version 2008-03-03
 */
public class SaleDAO
{
    
  /////////////////////////////////////////////
  ///   Singleton code
  
  private static SaleDAO instance = null;
  
  /** Creates a new instance of SaleDAO */
  private SaleDAO()
  {
  }//constructor

  /** Retrieves the single instance of this class */
  public static synchronized SaleDAO getInstance()
  {
    if (instance == null)
    {
      instance = new SaleDAO();
    }
    return instance;
  }//getInstance
  
  
  ////////////////////////////////////////////
  ///   CREATE methods
  
  /** Creates a new sale in the database */
   public Sale create(String id) throws DataException
  {
    Sale sale = new Sale(id);
    sale.setObjectAlreadyInDB(false);
    Cache.getInstance().put(sale.getId(), sale);
    return sale;
  }//create


  ////////////////////////////////////////////
  ///   READ methods

  /**
   * Reads an existing Physical Car from the database
   * 1) Checks cache
   * 2) Gets a connection
   * 3) Calls read with connection argument
   */
  public Sale read(String id) throws DataException
  {
    if (Cache.getInstance().containsKey(id))
    {
      return (Sale)Cache.getInstance().get(id);
    }
    Connection conn = ConnectionPool.getInstance().get();
    try
    {
      return read(id, conn);
    }
    catch (Exception e)
    {
      throw new DataException("An error occurred while reading the business object information.", e);
    }
    finally
    {
      ConnectionPool.getInstance().release(conn);
    }
  }

  /**
   * Internal method to read an existing Physical Car from the database
   * 1) Check cache for object
   * 2) Create a prepared statement to execute sql query
   * 3) Create a resultset from the preparedstatement
   * 4) Return the Physical Car
   */
  synchronized Sale read(String id, Connection conn) throws Exception
  {
    if (Cache.getInstance().containsKey(id))
    {
      return (Sale)Cache.getInstance().get(id);
    }
    PreparedStatement stmt = conn.prepareStatement("select * FROM sale WHERE sale.id = ?");
    try
    {
      stmt.setString(1, id);
      ResultSet rs = stmt.executeQuery();
      if (rs.next())
      {
        return readRecord(rs, conn);
      }//if
      throw new DataException("Sale with id '" + id + "' not found.");
    }
    finally
    {
      stmt.close();
    }
  }//read


  /**
   * Internal method to create a Physical Car object from a record
   * 1) Check cache for object
   * 2) Instantiate new Physical Car
   * 3) Set alreadyInDb to false
   * 4) Set all variables from result set
   * 5) Set dirty to true
   * 6) Return Physical Car
   */
  synchronized Sale readRecord(ResultSet rs, Connection conn) throws Exception
  {
    Sale sale = new Sale(rs.getString("id"));
    sale.setObjectAlreadyInDB(true);
    sale.setTotalPrice(Double.parseDouble(rs.getString("TOTAL_PRICE")));
    sale.setTax(Double.parseDouble(rs.getString("TAX")));
    sale.setFinanced((rs.getString("FINANCED")));
    sale.setDownPayment(Double.parseDouble(rs.getString("DOWN_PAYMENT")));
    sale.setDiscount(Double.parseDouble(rs.getString("DISCOUNT")));
    sale.setPaymentPlan(rs.getString("PAYMENT_PLAN"));
    sale.setDate(rs.getDate("SALE_DATE"));
    sale.setSalesPersonId(rs.getString("SALESPERSON_ID"));

    Cache.getInstance().put(sale.getId(), sale);
    sale.setDirty(false);
    return sale;
  }//readRecord

  /////////////////////////////////////////////
  ///   UPDATE methods

  /** Saves an existing sale in the database */
  public void save(Sale sale) throws DataException
  {
    Connection conn = ConnectionPool.getInstance().get();
    try
    {
      save(sale, conn);
      conn.commit();
    }
    catch (Exception e)
    {
      try
      {
        conn.rollback();
      }
      catch (SQLException e2)
      {
        throw new DataException("Could not roll back the database transaction!", e2);
      }
      throw new DataException("An error occurred while saving the business object information.", e);
    }
    finally
    {
      ConnectionPool.getInstance().release(conn);
    }
  }

  /** Internal method to update a sale in the database */
  void save(Sale sale, Connection conn) throws Exception
  {
    Cache.getInstance().put(sale.getId(), sale);
    if (sale.isObjectAlreadyInDB() && sale.isDirty())
    {
      update(sale, conn);
    }
    else if (sale.isDirty())
    {
      insert(sale, conn);
    }

    List<SaleLine> sl = sale.getSalesLines();

    for (SaleLine si: sl)
    {
      SaleLineDAO.getInstance().save(si, conn);
    }
  }

  /** Saves an existing sale to the database */
  private void update(Sale sale, Connection conn) throws Exception
  {
    PreparedStatement stmt = conn.prepareStatement("UPDATE sale SET total_price=?,tax=?, financed=?, down_payment=?, payment_plan=?, discount=?, sale_date=?, salesperson_bonus=?, salesperson_id=?  WHERE id=?");
    try
    {
      stmt.setString(1, sale.getTotalPrice() + "");
      stmt.setString(2, sale.getTax() + "");
      stmt.setString(3, sale.getFinanced() + "");
      stmt.setString(4, sale.getDownPayment() + "");
      stmt.setString(5, sale.getPaymentPlan() + "");
      stmt.setString(6, sale.getDiscount() + "");
      stmt.setString(7, sale.getDate() + "");
      stmt.setString(8, sale.getSalespersonBonus() + "");
      stmt.setString(9, sale.getSalesPersonId());
      
      stmt.execute();
      sale.setDirty(false);
    }
    finally
    {
      stmt.close();
    }
  }

  /** Inserts a new sale into the database */
  private void insert(Sale sale, Connection conn) throws Exception
  {
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO physical_car VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    try
    {
      stmt.setString(1, sale.getId());
      stmt.setString(2, sale.getTotalPrice() + "");
      stmt.setString(3, sale.getTax() + "");
      stmt.setString(4, sale.getFinanced() + "");
      stmt.setString(5, sale.getDownPayment() + "");
      stmt.setString(6, sale.getPaymentPlan() + "");
      stmt.setString(7, sale.getDiscount() + "");
      stmt.setString(8, sale.getDate() + "");
      stmt.setString(9, sale.getSalespersonBonus() + "");
      stmt.setString(10, sale.getSalesPersonId());
      stmt.execute();
      sale.setObjectAlreadyInDB(true);
      sale.setDirty(false);
    }
    finally
    {
      stmt.close();
    }

  }


  /////////////////////////////////////////////////
  ///   DELETE methods

  /** We do not support deleting of business objects in this application */
  public void delete(Sale sale) throws DataException
  {
    throw new RuntimeException("Nice try. The delete function is not supported in this application.");
  }
  

  
  ////////////////////////////////////////////////
  ///   SEARCH methods
  
  /** Retrieves all dayofweeks from the database */
  public List<Sale> getAll() throws DataException
  {
    Connection conn = ConnectionPool.getInstance().get();
    try
    {
      PreparedStatement stmt = conn.prepareStatement("SELECT *  FROM sale");
      return search(stmt, conn);
    }
    catch (Exception e)
    {
      try
      {
        conn.rollback();
      }
      catch (SQLException e2)
      {
        throw new DataException("Could not roll back the database transaction!", e2);
      }
      throw new DataException("An error occurred while reading the business object information.", e);
    }
    finally
    {
      ConnectionPool.getInstance().release(conn);
    }
  }
  
  /** Internal method to search by certain fields */
  private List<Sale> search(PreparedStatement stmt, Connection conn) throws Exception
  {
    List<Sale> saleList = new LinkedList<Sale>();
    try
    {
      ResultSet rs = stmt.executeQuery();
      while (rs.next())
      {
        saleList.add(readRecord(rs, conn));
      }
    }
    finally
    {
      stmt.close();
    }
    return saleList;
  }
  
  
  
}//class
